﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using ECS.DAL.Entity;

namespace ECS.DAL.Component
{
    public class CommentRecordComponent : BaseSQLModel
    {
        #region private properties
        private const string SQL_QUERY = "SELECT cm.comment_id, cm.slip_id, cm.comment_type_id, cm.comment_number, cm.[value], cm.create_date, cm.create_by, ct.comment_name FROM t_eics_comment cm join t_eics_comment_type ct on ct.comment_type_id=cm.comment_type_id  WHERE cm.slip_id=@slip_id order by cm.create_date ";

        private const string SQL_PROC_QUERY = " ";

        private const string SQL_INSERT = " ";

        private const string SQL_PROC_INSERT = "usp_InsertCommentRecord";
                                    //@AppId,  " +
                                    //" @SlipId, @CommentTypeId, @CommentNumber, @Value, " +
                                    //" @Device, @UserId "; 

        private const string SQL_UPDATE = " ";

        private const string SQL_PROC_UPDATE = "usp_UpdateCommentRecord";

        private const string SQL_DELETE = " ";

        private const string SQL_GET_LIST_COMMENT_TYPE = "usp_get_data_to_dropdownlist_1col";
        #endregion

        #region public properties
        /// <summary>
        /// Error result
        /// </summary>
        public ErrorInfo ErrorMessage { set; get; }
        #endregion

        #region constructor
        public CommentRecordComponent()
        {
            ErrorMessage = new ErrorInfo();
        }
        #endregion

        #region private methods
        
        #endregion

        #region public mehods
        /// <summary>
        /// get Eics setup data to list
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public List<ObjectContext> Search(ObjectContext parameter)
        {
            //cm.comment_id, cm.slip_id, cm.comment_type_id, cm.comment_number, cm.[value], create_date, create_by, ct.comment_name
            CommentRecord param = (CommentRecord)parameter;
            List<ObjectContext> items = new List<Entity.ObjectContext>();
            using (Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                //sqlString.Append(SQL_PROC_QUERY);
                sqlString.Append(SQL_QUERY);

                //if (!string.IsNullOrEmpty(param.SetupId))
                //{
                //    sqlString.Append(SQL_PARAM_SEARCH_SETUP_ID);
                //    sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(param.SetupId));
                //}

                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                //cmd.CommandType = System.Data.CommandType.StoredProcedure;
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", param.AppId));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", param.Device));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", param.UserId));
                cmd.Parameters.Add(entities.CreateParameter("@slip_id", param.SlipId));
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    CommentRecord data = new CommentRecord();
                    data.CommentId = base.GetString(0, reader);
                    data.SlipId = base.GetString(1, reader);
                    data.CommentTypeId = base.GetString(2, reader);
                    data.CommentNumber = (int)base.GetInt(3, reader);
                    data.Value = base.GetString(4, reader);
                    data.CreateDate = (DateTime)base.GetDate(5, reader);
                    data.CreateBy = base.GetString(6, reader);
                    data.CommentTypeName = base.GetString(7, reader);
                    data.NotChangeObject();
                    items.Add(data);
                }
            }
            return items;
        }
        /// <summary>
        /// get comment type for dropdown list
        /// </summary>
        /// <returns></returns>
        public List<ComboItem> GetCommentTypeListForDropdown()
        { 
            List<ComboItem> items = new List<ComboItem>();
            using (Entities entities = new Entities())
            {
                StringBuilder sqlString = new StringBuilder();
                sqlString.Append(SQL_GET_LIST_COMMENT_TYPE);

                //cmd.Parameters.AddWithValue("@TableName", "t_eics_comment_type");
                //cmd.Parameters.AddWithValue("@col1", "comment_name");
                //cmd.Parameters.AddWithValue("@col2", "comment_type_id");

                entities.Open();
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add(entities.CreateParameter("@TableName", "t_eics_comment_type"));
                cmd.Parameters.Add(entities.CreateParameter("@col1", "comment_name"));
                cmd.Parameters.Add(entities.CreateParameter("@col2", "comment_type_id"));
                DbDataReader reader = cmd.ExecuteReader();
                //DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                //adapter.Fill(dsResult);
                while(reader.Read())
                {
                    ComboItem item = new ComboItem();
                    item.text = GetString(0, reader);
                    item.value = GetString(1, reader);
                    items.Add(item);
                }
            }
            return items;
        }
        public override string GetInsertSQL(ObjectContext param)
        {
            /*@AppId, @SlipId, @SlipId, @DimensionNumber, @Length, @Weight, @Height, @PeiceCount, @Device, @UserId "; */
            CommentRecord parameter = (CommentRecord)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_PROC_INSERT);
            //sqlString.Replace("@AppId", BaseSQLModel.SqlWithQoute(parameter.AppId));
            //sqlString.Replace("@SlipId", BaseSQLModel.SqlWithQoute(parameter.SlipId));
            //sqlString.Replace("@CommentTypeId", BaseSQLModel.SqlWithQoute(parameter.CommentTypeId));
            //sqlString.Replace("@CommentNumber", BaseSQLModel.SqlWithoutQoute(parameter.CommentNumber));
            //sqlString.Replace("@Value", BaseSQLModel.SqlWithQoute(parameter.Value));
            //sqlString.Replace("@Device", BaseSQLModel.SqlWithQoute(parameter.Device));
            //sqlString.Replace("@UserId", BaseSQLModel.SqlWithQoute(parameter.UserId));

            return sqlString.ToString();
        }

        public override string GetUpdateSQL(ObjectContext param)
        {
            CommentRecord parameter = (CommentRecord)param;
            StringBuilder sqlString = new StringBuilder();
            //sqlString.Append(SQL_UPDATE);
            sqlString.Append(SQL_PROC_UPDATE);
            sqlString.Replace("@in_vchApplicationID", BaseSQLModel.SqlWithQoute(parameter.AppId));
            
            sqlString.Replace("@in_vchSlipID", BaseSQLModel.SqlWithQoute(parameter.SlipId));
            sqlString.Replace("@in_intCommentNumber", BaseSQLModel.SqlWithoutQoute(parameter.CommentNumber.ToString()));
            sqlString.Replace("@in_vchCommentID", BaseSQLModel.SqlWithQoute(parameter.CommentId));
            sqlString.Replace("@in_vchCommentTypeID", BaseSQLModel.SqlWithQoute(parameter.CommentTypeId));
            sqlString.Replace("@in_vchValue", BaseSQLModel.SqlWithQoute(parameter.Value));

            sqlString.Replace("@in_vchDevice", BaseSQLModel.SqlWithQoute(parameter.Device));
            sqlString.Replace("@in_vchUserID", BaseSQLModel.SqlWithQoute(parameter.UserId));

            return sqlString.ToString();
        }

        public override string GetDeleteSQL(ObjectContext param)
        {
            CommentRecord parameter = (CommentRecord)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            //sqlString.Append(SQL_DELETE);
            //sqlString.Replace("@setup_id", BaseSQLModel.SqlWithQoute(parameter.SetupId));
            return sqlString.ToString();
        }
        /// <summary>
        /// Insert to data source
        /// </summary>
        /// <param name="param"></param>
        public override void Insert(ObjectContext param)
        {
            DataSet ds = new DataSet();
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetInsertSQL(param));
                    DbDataAdapter adapter = entities.CreateDataAdapter(GetInsertSQL(param));
                    adapter.Fill(ds, "Result");
                    //entities.SetCommand(cmd);
                    //int m_iRecordChanged = entities.SaveChanges();
                    //if (m_iRecordChanged < 1)
                    if(ds.Tables[0].Rows.Count > 0)
                    {
                        ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                        ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                        ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                        ErrorMessage.ID = string.Empty;
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// update to datasource
        /// </summary>
        /// <param name="param"></param>
        public override void Update(ObjectContext param)
        {
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetUpdateSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 1)
                    {
                        throw new Exception("comment record update fail.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// delete from datasource
        /// </summary>
        public override void Delete(ObjectContext param)
        {
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetDeleteSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 0)
                    {
                        throw new Exception("comment record delete fail.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// special case for store procedure to update data that return result as record
        /// </summary>
        /// <param name="items"></param>
        public override void SaveChanges(List<ObjectContext> items)
        {
            CommentRecord parameter = null;
            string sqlCmd = string.Empty;
            string textMessage = string.Empty;
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DataSet ds = new DataSet();
                    DbCommand cmd = entities.CreateCommand(sqlCmd);

                    DbDataAdapter adapter = entities.CreateDataAdapter(sqlCmd);
                    adapter.SelectCommand = cmd;
                    //cmd.CommandType = CommandType.StoredProcedure;
                    foreach (ObjectContext item in items)
                    {
                        parameter = (CommentRecord)item;
                        switch (item.GetObjectSate())
                        {
                            case OBJECT_STATE.Insert:
                                sqlCmd = GetInsertSQL(item);
                                textMessage = "Insert ";
                                cmd.CommandText = sqlCmd;
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", parameter.SlipId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchCommentTypeID", parameter.CommentTypeId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_intCommentNumber", parameter.CommentNumber));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchValue", parameter.Value));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                                break;
                            case OBJECT_STATE.Update:
                                sqlCmd = GetUpdateSQL(item);
                                textMessage = "Update ";
                                cmd.CommandText = sqlCmd;
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.Clear();
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchCommentID", parameter.CommentId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", parameter.SlipId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchCommentTypeID", parameter.CommentTypeId));
                                cmd.Parameters.Add(entities.CreateParameter("@in_intCommentNumber", parameter.CommentNumber));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchValue", parameter.Value));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                                cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                                break;
                            case OBJECT_STATE.Delete:
                                sqlCmd = GetDeleteSQL(item);
                                textMessage = "Delete ";
                                //cmd.CommandText = sqlCmd;
                                //cmd.CommandType = CommandType.StoredProcedure;
                                //cmd.Parameters.Clear();
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchCommentID", parameter.CommentId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchSlipID", parameter.SlipId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchCommentTypeID", parameter.CommentTypeId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_intCommentNumber", parameter.CommentNumber));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchValue", parameter.Value));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                                break;
                            default:
                                sqlCmd = string.Empty;
                                break;
                        }
                        if (!string.IsNullOrEmpty(sqlCmd))
                        {
                            adapter.Fill(ds);
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                //if (!ds.Tables[0].Rows[0]["Status"].ToString().Equals("PASS"))
                                //{
                                //    string errorMessage = string.Format("Status: {0} Error Code:{1}\r\nError:{2}", ds.Tables[0].Rows[0]["Status"].ToString(), ds.Tables[0].Rows[0]["ErrorCode"].ToString(), ds.Tables[0].Rows[0]["Message"].ToString());
                                //    throw new Exception(errorMessage);
                                //}
                                ErrorMessage.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                                ErrorMessage.ErrorCode = ds.Tables[0].Rows[0]["ErrorCode"].ToString();
                                ErrorMessage.Message = ds.Tables[0].Rows[0]["Message"].ToString();
                            }
                            //item.NotChangeObject();
                        }
                    }
                }
                transaction.Complete();
                items.RemoveAll(o => o.GetObjectSate() == OBJECT_STATE.Delete);
                foreach (ObjectContext data in items)
                {
                    if (data.GetObjectSate() != OBJECT_STATE.None)
                    {
                        data.NotChangeObject();
                    }
                }
            }
        }
        #endregion
    }
}
